using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Models;

namespace DAL
{
    public static partial class LeftLinkService
    {
        public static bool AddLeftLink(int adminId, int typeId, int typeLinkId)
        {
            string sql =
                "INSERT LeftLink (UserID, TypeID, TypeLinkId)" +
                "VALUES (@UserID, @TypeID, @TypeLinkId)";

            sql += " ; SELECT @@IDENTITY";


            SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@UserID", adminId),
				new SqlParameter("@TypeID", typeId),
                new SqlParameter("@TypeLinkId", typeLinkId)
			};

            int i = DBHelper.ExecuteNonQuery(CommandType.Text, sql, para);
            if (i > 0)
            {
                return true;
            }
            else
            {
                return false;
            }

        }


        public static bool DeleteLeftLinkByLeftID(int leftID)
        {
            string sql = "DELETE LeftLink WHERE LeftID = @LeftID";

            SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@LeftID", leftID)
			};

            int i = DBHelper.ExecuteNonQuery(CommandType.Text, sql, para);
            if (i > 0)
            {
                return true;
            }
            else
            {
                return false;
            }

        }

        public static bool DeleteLeftLink(int userID, int typeID)
        {
            string sql = "DELETE LeftLink WHERE UserID = @UserID and TypeID = @TypeID";

            SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@UserID", userID),
                new SqlParameter("@TypeID", typeID)
			};

            int i = DBHelper.ExecuteNonQuery(CommandType.Text, sql, para);
            if (i > 0)
            {
                return true;
            }
            else
            {
                return false;
            }

        }

        public static bool ModifyLeftLink(LeftLink leftLink)
        {
            string sql =
                "UPDATE LeftLink " +
                "SET " +
                    "UserID = @UserID, " +
                    "TypeID = @TypeID, " +
                    "TypeLinkId = @TypeLinkId " +
                "WHERE LeftID = @LeftID";

            SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@LeftID", leftLink.LeftID),
				new SqlParameter("@UserID", leftLink.UserID),
				new SqlParameter("@TypeID", leftLink.TypeID),
                new SqlParameter("@TypeLinkId", leftLink.TypeLinkId)
			};

            int i = DBHelper.ExecuteNonQuery(CommandType.Text, sql, para);
            if (i > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public static IList<LeftLink> GetAllLeftLinks()
        {
            string sqlAll = "SELECT * FROM LeftLink";
            return GetLeftLinksBySql(sqlAll);
        }

        public static IList<LeftLink> GetLeftLinksByUserID(int userID)
        {
            string sqlAll = "SELECT * FROM LeftLink where UserID = @UserID";
            SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@UserID", userID)
			};

            return GetLeftLinksBySql(sqlAll, para);
        }

        public static IList<LeftLink> GetLeftLinksByUserID3(int userID)
        {
            string sqlAll = "SELECT * FROM LeftLink where UserID = @UserID and TypeLinkId = 0";
            SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@UserID", userID)
			};

            return GetLeftLinksBySql(sqlAll, para);
        }

        public static IList<LeftLink> GetLeftLinksByUserID6(int userID, int typeId)
        {
            string sqlAll = "SELECT * FROM LeftLink where UserID = @UserID and TypeLinkId= @TypeLinkId";
            SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@UserID", userID),
                new SqlParameter("@TypeLinkId", typeId)
			};

            return GetLeftLinksBySql(sqlAll, para);
        }

        public static LeftLink GetLeftLinkByLeftID(int leftID)
        {
            string sql = "SELECT * FROM LeftLink WHERE LeftID = @LeftID";

            SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@LeftID", leftID)
			};

            DataTable temp = DBHelper.ExecuteGetDataTable(CommandType.Text, sql, para);

            LeftLink leftLink = new LeftLink();

            foreach (DataRow dr in temp.Rows)
            {
                leftLink.LeftID = (int)dr["LeftID"];
                leftLink.UserID = (int)dr["UserID"];
                leftLink.TypeID = (int)dr["TypeID"];
                leftLink.TypeLinkId = (int)dr["TypeLinkId"];

            }
            return leftLink;

        }


        private static IList<LeftLink> GetLeftLinksBySql(string safeSql)
        {
            List<LeftLink> list = new List<LeftLink>();

            using (SqlDataReader dr = DBHelper.ExecuteGetReader(CommandType.Text, safeSql, null))
            {
                while (dr.Read())
                {
                    LeftLink leftLink = new LeftLink();

                    leftLink.LeftID = (int)dr["LeftID"];
                    leftLink.UserID = (int)dr["UserID"];
                    leftLink.TypeID = (int)dr["TypeID"];
                    leftLink.TypeLinkId = (int)dr["TypeLinkId"];

                    list.Add(leftLink);
                }
            }
            return list;
        }

        private static IList<LeftLink> GetLeftLinksBySql(string safeSql, params SqlParameter[] para)
        {
            List<LeftLink> list = new List<LeftLink>();

            using (SqlDataReader dr = DBHelper.ExecuteGetReader(CommandType.Text, safeSql, para))
            {
                while (dr.Read())
                {
                    LeftLink leftLink = new LeftLink();

                    leftLink.LeftID = (int)dr["LeftID"];
                    leftLink.UserID = (int)dr["UserID"];
                    leftLink.TypeID = (int)dr["TypeID"];
                    leftLink.TypeLinkId = (int)dr["TypeLinkId"];

                    list.Add(leftLink);
                }
            }
            return list;
        }
    }
}
